<?php

class Node_Model extends CI_Model {

    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Get node detail by node id
     * @param <int> $nodeID
     * @return <array>
     */
    public function getNodeDetail($nodeID)
    {
        $query = 'SELECT nodes.*,node_fields.*,categories.category_id, categories.title AS category_title ';
        $query.= 'FROM nodes, node_fields, categories ';
        $query.= 'WHERE nodes.node_id = '. $nodeID .' ';
        $query.= 'AND nodes.node_id = node_fields.node_id ';
        $query.= 'AND nodes.category_id = categories.category_id';

        //Call query
        $result = $this->db->query($query)->row_array();

        //Update total view
        $this->db->where('node_id', $nodeID)->update('nodes', array('view' => $result['view']+1));

        return $result;
    }

    /**
     * Get List Node By CategoryID
     * @param <type> $categoryID
     * @param <type> $limit
     * @param <type> $offset
     * @return <type>
     */
    public function getListNodeByCategoryID($categoryID, $limit, $offset)
    {
        $query = 'SELECT SQL_CALC_FOUND_ROWS * FROM nodes ';
        $query.= 'WHERE category_id = '. $categoryID .' ';
        $query.= 'ORDER BY node_id DESC LIMIT '. $offset .', '. $limit;

        $result['data'] = $this->db->query($query)->result_array();

        $query = 'SELECT FOUND_ROWS() AS total';

        $total  = $this->db->query($query)->row_array('total');

        $result['total'] = $total['total'];

        return $result;
    }

    /**
     * Get list node by parentID
     * @param <type> $parentID
     * @param <type> $limit
     * @param <type> $offset
     * @return <type>
     */
    public function getListNodeByParentID($parentID, $limit, $offset)
    {
        $query = 'SELECT SQL_CALC_FOUND_ROWS nodes.*,categories.title AS category_title ';
        $query.= 'FROM nodes, categories ';
        $query.= 'WHERE categories.parent_id = '. $parentID .' ';
        $query.= 'AND categories.category_id = nodes.category_id ';
        $query.= 'ORDER BY nodes.node_id DESC LIMIT '. $offset .', '. $limit;

        $result['data'] = $this->db->query($query)->result_array();

        $query = 'SELECT FOUND_ROWS() AS total';

        $total  = $this->db->query($query)->row_array('total');

        $result['total'] = $total['total'];

        return $result;
    }

    /**
     * Get list node older by nodeID
     * @param <type> $nodeID
     * @param <type> $limit
     * @return <type>
     */
    public function getListNodeOlder($nodeID, $limit = 5)
    {
        //Get categoryID
        $categoryID = $this->db->select('category_id')->where(array('node_id' => $nodeID))->get('nodes')->row_array();

        $query = 'SELECT * FROM nodes ';
        $query.= 'WHERE node_id < '.$nodeID.' ';
        $query.= 'AND category_id = '.$categoryID['category_id'].' ';
        $query.= 'ORDER BY node_id DESC LIMIT '.$limit;

        //Call query
        $result = $this->db->query($query)->result_array();

        return $result;
    }

    /**
     * Get list randome node by cagegoryID
     * @param <type> $categoryID
     * @param <type> $limit
     */
    public function getListRandNodeByCategoryID($categoryID, $limit)
    {
        $query = 'SELECT * FROM nodes ';
        $query.= 'WHERE category_id = '.$categoryID.' ';
        $query.= 'ORDER BY RAND() LIMIT '.$limit;

        //Call query
        $result = $this->db->query($query)->result_array();

        return $result;
    }

    /**
     * Get list hot node
     * @param <type> $limit
     */
    public function getListHotNode($limit)
    {
        $query = 'SELECT * FROM nodes ';
        $query.= 'ORDER BY view DESC LIMIT '.$limit;

        //Call query
        $result = $this->db->query($query)->result_array();

        return $result;
    }

    /**
     * Get list hot node by categoryID
     * @param <type> $categoryID
     * @param <type> $limit
     * @param <type> $offset
     */
    public function getListHotNodeByCategoryID($categoryID, $limit, $offset)
    {
        $query = 'SELECT * FROM nodes ';
        $query.= 'WHERE category_id = '.$categoryID.' ';
        $query.= 'ORDER BY view DESC LIMIT '.$offset.', '.$limit;

        //Call query
        $result = $this->db->query($query)->result_array();

        return $result;
    }

    /**
     * Get list event
     * @param <type> $categoryID
     * @param <type> $limit
     * @param <type> $offset
     * @return <type>
     */
    public function getListEvent($categoryID, $limit, $offset)
    {
        $query = 'SELECT * FROM nodes, node_fields ';
        $query.= 'WHERE nodes.category_id = '. $categoryID .' ';
        $query.= 'AND nodes.node_id = node_fields.node_id ';
        $query.= 'ORDER BY nodes.node_id DESC LIMIT '. $offset .', '. $limit;

        $result = $this->db->query($query)->result_array();

        return $result;
    }

    /**
     * Search data
     * @param <type> $text
     * @param <type> $limit
     * @param <type> $offset
     */
    public function searchData($text, $limit, $offset)
    {
        $query = 'SELECT SQL_CALC_FOUND_ROWS nodes.*,categories.title AS category_title ';
        $query.= 'FROM nodes, categories ';
        $query.= 'WHERE nodes.title LIKE \'%'. $text .'%\' ';
        $query.= 'AND categories.category_id = nodes.category_id ';
        $query.= 'ORDER BY nodes.node_id DESC LIMIT '. $offset .', '. $limit;

        $result['data'] = $this->db->query($query)->result_array();

        $query = 'SELECT FOUND_ROWS() AS total';

        $total  = $this->db->query($query)->row_array('total');

        $result['total'] = $total['total'];

        return $result;
    }
    
    /**
     * Get List Advertising
     * @param <string> type
     * @param <int> $limit
     * @param <int> $offset
     * @return <array>
     */
    public function getListAdvertising($categoryID, $type, $limit, $offset)
    {
        $query = 'SELECT node_fields.link, node_fields.value  FROM nodes, node_fields ';
        $query.= 'WHERE nodes.category_id = '. $categoryID .' ';
        $query.= 'AND nodes.node_id = node_fields.node_id ';
        $query.= 'AND nodes.status = 1 ';
        $query.= 'AND node_fields.type = "'. $type .'" ';
        $query.= 'ORDER BY nodes.node_id DESC LIMIT '. $offset .', '. $limit;
        
        $result = $this->db->query($query)->result_array();

        return $result;
    }

}

?>